# numbers = [1, 2, 3, 4, 5, 6, 17, 23, 34, 37, 42,  56, 65, 71, 89, 92]

# new_numbers = [16,  31, 30, 33, 347]

# numbers = [16, 74, 48, 28, 30, 347, 31]
numbers = [5, 6, 12, 16, 17, 18, 19, 22, 24, 25, 27, 28, 29, 30, 32, 33, 34, 36, 37, 38, 39, 42, 48, 56, 61, 65, 71, 89, 92, 171, 232, 347, 371]




for num in numbers:
    sql = f'''
    #{num}号用户,event_review
    INSERT INTO like_notifs_cumu_new (resource_type, target_id, create_time, update_time,
                                    receiver_user_id, source_user_id_1, source_user_id_2, total_like_count)
    WITH ranked_likes AS (SELECT ucl.target_id,
                                ucl.user_id,
                                ucl.create_time,
                                ROW_NUMBER() OVER (
                                    PARTITION BY ucl.target_id
                                    ORDER BY ucl.create_time
                                    ) AS rn
                        FROM user_resource_likes_new ucl
                        WHERE ucl.resource_type = 4
                        and ucl.target_id IN (SELECT review_id
                                                FROM event_review
                                                WHERE user_id = {num})
                        AND ucl.user_id <> {num})
    SELECT 4                                            AS resource_type,
        rl.target_id                                 AS target_id,
        MIN(rl.create_time)                          AS create_time,
        MAX(rl.create_time)                          AS update_time,
        {num}                                           AS receiver_user_id,
        MAX(CASE WHEN rl.rn = 1 THEN rl.user_id END) AS source_user_id_1,
        MAX(CASE WHEN rl.rn = 2 THEN rl.user_id END) AS source_user_id_2,
        COUNT(*)                                     AS total_like_count
    FROM ranked_likes rl
    GROUP BY rl.target_id;'''
    print(sql)

